# -*- coding: utf-8 -*-
# @File    : testaaaa.py
# @Date    : 2021-11-04
# @Author  : Richard

import os
import re
import time

# 解析SQL文件
import uuid

import dmPython
import pymssql


class ResoluSql(object):
    def __init__(self, filepath):
        self.filepath = filepath

    #
    def ergodic_sql(self):
        """
        :param:sql_file_path
        :return:  sqls info list
        """
        try:
            with open(self.filepath)  as f:
                sqls = f.readlines(100000)
                return sqls
        except Exception as Err:
            print("ergodic statement error is :{0}".format(Err))

    # 解析SQL文件
    def get_sql_file(self):
        """
        功能：根据目录遍历SQL文件
        :param:sql_file_path
        :return:  SQL file ，data type is list
        """
        try:
            for root, dirs, files in os.walk(self.filepath):
                data = []
                for file in files:
                    res = os.path.join(root, file).encode("utf8")
                    data.append(res)
                return data
        except Exception as Err:
            print("ergodic statement error is :{0}".format(Err))

    def get_sql_file_sort(self):
        """
        功能：根据目录遍历SQL文件
        :param:sql_file_path
        :return:  SQL file ，data type is list
        """
        try:
            file_list = os.listdir(self.filepath)
            # 使用匿名函数截取数
            file_list.sort(key=lambda x: int(str(x).split(".")[0][0]))
            data = []
            for i in file_list:
                data.append(i)
            print("data:{0}".format(data))
        except Exception as Err:
            print("ergodic statement error is :{0}".format(Err))

    # 获取SQL文件中SQL语句
    def open_sql_file(self, sql_file):
        """
        功能：根据SQL文件解析出SQL语句
        :param sql_file:
        :return: sql statement list
        """
        data = []
        if sql_file:
            try:
                # with open(sql_file, encoding="GBK") as f:
                with open(sql_file, encoding="utf8") as f:
                    res = f.read().split("\n")
                    if res:
                        return res
            except Exception as Err:
                print("open sql file error,information more check[0]".format(Err))
        else:
            print("sql file not exists")


class DB(object):
    def __init__(self, sql, ip, user, pwd, port):
        self.sql = sql
        self.ip = ip
        self.user = user
        self.port = port
        self.pwd = pwd

    def run_sql(self):
        status = 0
        try:
            # ip_list = ["130.20.163.150", "130.20.164.17", "130.20.164.217", "130.20.163.199", "130.20.164.166",
            #            "130.20.164.252", "130.20.164.113", "130.20.164.216"]
            # if ip in ip_list:
            #     re_pwd = "Dmdba@123"
            # else:
            #     # re_pwd = "Dmdba@963"
            #     re_pwd = "Dmdba@123"
            # re_pwd = "Dmdba@963"
            conn = dmPython.connect(user=self.user, password=self.pwd, server=self.ip, port=self.port)
            cursor = conn.cursor()
            cursor.execute(self.sql)
            cursor.close()
            conn.commit()
            conn.close()
            status = 1
            return status
        except Exception as  Err:
            print("数据库执行SQL发生错误：{0}".format(Err))
            return status


class MSSQL(object):
    def __init__(self, host, user, pwd, db):
        self.host = host
        self.user = user
        self.pwd = pwd
        self.db = db

    def __GetConnect(self):
        if not self.db:
            raise (NameError, "没有设置数据库信息")
        self.conn = pymssql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db, charset="utf8")
        cur = self.conn.cursor()
        if not cur:
            raise (NameError, "连接数据库失败")
        else:
            return cur

    def ExecQuery(self, sql):
        cur = self.__GetConnect()
        cur.execute(sql)
        resList = cur.fetchall()

        # 查询完毕后必须关闭连接
        self.conn.close()
        return resList

    def ExecNonQuery(self, sql):
        cur = self.__GetConnect()
        cur.execute(sql)
        self.conn.commit()
        self.conn.close()


if __name__ == '__main__':
    # list_data = [1,12,45,78,3,4,5,6,2,8,9,10,3,3,5,6,9]
    # # 去重,同时升序
    # unique_dict = set(list_data)
    # print(unique_dict)
    date = "2021-12-17 09:41:20"
    timeArray = time.strptime(date,"%Y-%m-%d %H:%M:%S")
    print(timeArray)
    print(type(timeArray))
    timestamp = time.mktime(timeArray)
    print(timestamp)



    # 维护一个台胀，哪个单位已执行哪个文件，若是在台胀里面已经执行了就跳过，继续执行下一个单位
    # 若是注释和插入语句需要逐行执行
    # # 创建类语句
    # file_path = "D:\PycharmProjects\sqlRun\RunSqlToDm\sql"
    # # sql_file = "D:\PycharmProjects\sqlRun\RunSqlToDm\sql\A.sql"
    # resoluSql = ResoluSql(filepath=file_path)



    # sql = resoluSql.open_sql_file(sql_file=sql_file)
    # print("sqls:{0}".format(sql))
    # ip = "192.168.23.152"
    # user = "sysdba"
    # pwd = "Dmdba@963"
    # port = 5136
    # # db = DB(sql=sql, ip=ip, user=user, pwd=pwd, port=port)
    # # run_flag = db.run_sql()
    # # if int(run_flag) == 1:
    # #     print("执行成功")
    # # else:
    # #     print("执行失败")
    #
    # file_txt = "D:\PycharmProjects\sqlRun\RunSqlToDm\sql\jttywsh.txt"
    # data = resoluSql.open_sql_file(sql_file=file_txt)
    # ms = MSSQL(host="222.20.0.19", user="sa", pwd="gxzfoa@2016", db="Gw2016")
    # with open("D:\PycharmProjects\sqlRun\RunSqlToDm\logs\log.txt",mode="w")  as f:
    #     # print(len(data))
    #     for i in data:
    #         reslist = ms.ExecQuery("select count(1) from dzgwbswj where dzgwbswj.ywlsh = '{0}'".format(i))
    #         print("正文公文标识:"+str(i)+" 数量:"+str(reslist[0][0]))

    # a = uuid.uuid1()
    # print(str(a).replace("-",""))
    # print(len(str(a).replace("-","")))